<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Connecting to the SQLite database</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Ruby, SQLite, language, database connection, sqlite3">
<meta name="description" content="In this chapter of the SQLite Ruby tutorial, we 
create a connection to a database.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 

<div class="content">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Connecting to the SQLite database</h1>

<p>
This part of the SQLite Ruby tutorial will connect to a database and
do a few simple things with the database. 
</p>

<div class="center">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* top_horizontal */
google_ad_slot = "3327173442";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<h2>Before we start</h2>

<p>
The SQLite comes with the sqlite3 command line utility. It can be used to
issue SQL commands against a database. Now we are going to use the sqlite3 command 
line tool to create a new database. 
</p>

<pre>
$ sqlite3 test.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
</pre>

<p>
We provide a parameter to the sqlite3 tool. The test.db is a 
database name. It is a single file on our disk. If it is present, 
it is opened. If not, it is created. 
</p>


<pre>
sqlite> .tables
sqlite> .exit
$ ls
test.db
</pre>

<p>
The <code>.tables</code> command gives a list of tables in the test.db
database. There are currently no tables. The <code>.exit</code> command
terminates the interactive session of the sqlite3 command line tool. 
The ls Unix command shows the contents of the current working directory. 
We can see the test.db file. All data will be stored in this single file.
</p>

<hr class="btm">

<p>
The <b>sqlite-ruby</b> interface is used to interact with SQLite database with
the Ruby language.
</p>

<pre>
$ sudo apt-get install libsqlite3-ruby
</pre>

<p>
The above command installs the module on Debian based Linux systems.
</p>

<p>
The first step is to create a Database object. The Database class encapsulates a 
single connection to an SQLite database. The database object is closed 
with the <code>close</code> method.
</p>

<pre>
SQLite3::Database.new dbname
SQLite3::Database.open dbname
</pre>

<p>
The <code>new</code> method creates a new Database object that opens the given
dbname file. If the file does not exist, it will be created if possible.
By default, the new database will return result rows as arrays.
The <code>open</code> method opens the database contained in the given file. 
</p>

<pre>
SQLite3::Database.new ":memory:"
</pre>

<p>
It is possible to create an in-memory database if we provide a special 
string ":memory:" for the file name.
</p>

<h2>Sources</h2>

<p>
The <a href="http://sqlite-ruby.rubyforge.org/">sqlite-ruby.rubyforge.org</a>
website was consulted when creating this tutorial.
</p>

<h2>Version</h2>

<p>
In the first code example, we will get the version of the SQLite database.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.new ":memory:"
    puts db.get_first_value 'SELECT SQLITE_VERSION()'
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end
</pre>

<p>
In the above Ruby script we create a new in-memory database. 
We execute an SQL statement which returns the version of the SQLite database. 
</p>

<pre class="explanation">
require 'sqlite3'
</pre>

<p>
We use sqlite3 Ruby module to connect to the SQLite database.
</p>

<pre class="explanation">
db = SQLite3::Database.new ":memory:"
</pre>

<p>
We create a new database object. The Database class encapsulates a single 
connection to an SQLite database. The database is created in memory. So it
is not permanent. 
</p>

<pre class="explanation">
puts db.get_first_value 'SELECT SQLITE_VERSION()'
</pre>

<p>
We call the <code>get_first_value</code> method of the db object. It executes the 
SQL statement and obtains the first value of the first row of a result set.
</p>

<pre class="explanation">
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
</pre>

<p>
We check for errors. This is important, since working with databases is
error prone. 
</p>

<pre class="explanation">
ensure
    db.close if db
end
</pre>

<p>
In the end, we release the resources. 
</p>

<pre>
$ ./version.rb
3.7.7
</pre>

<p>
The output might look like the above. 
</p>


<h2>Inserting data</h2>

<p>
We will create a Cars table and insert several rows to it.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
    db.execute "CREATE TABLE IF NOT EXISTS Cars(Id INTEGER PRIMARY KEY, 
        Name TEXT, Price INT)"
    db.execute "INSERT INTO Cars VALUES(1,'Audi',52642)"
    db.execute "INSERT INTO Cars VALUES(2,'Mercedes',57127)"
    db.execute "INSERT INTO Cars VALUES(3,'Skoda',9000)"
    db.execute "INSERT INTO Cars VALUES(4,'Volvo',29000)"
    db.execute "INSERT INTO Cars VALUES(5,'Bentley',350000)"
    db.execute "INSERT INTO Cars VALUES(6,'Citroen',21000)"
    db.execute "INSERT INTO Cars VALUES(7,'Hummer',41400)"
    db.execute "INSERT INTO Cars VALUES(8,'Volkswagen',21600)"
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end
</pre>

<p>
The above script creates a Cars table and inserts 8 rows into the
table. 
</p>

<pre class="explanation">
db = SQLite3::Database.open "test.db"
</pre>

<p>
We connect to the test.db database. 
</p>

<pre class="explanation">
db.execute "CREATE TABLE IF NOT EXISTS Cars(Id INTEGER PRIMARY KEY, 
    Name TEXT, Price INT)"
</pre>

<p>
The <code>execute</code> method executes the given SQL statement. 
A new Cars table is created if it does not already exist.
</p>

<pre class="explanation">
db.execute "INSERT INTO Cars VALUES(1,'Audi',52642)"
db.execute "INSERT INTO Cars VALUES(2,'Mercedes',57127)"
</pre>

<p>
These two lines insert two cars into the table. Note that by default, we are in
the <b>autocommit mode</b>, where all changes to the table are immediately effective. 
</p>

<pre>
sqlite> .mode column  
sqlite> .headers on
</pre>

<p>
We verify the written data with the sqlite3 tool. First we modify the
way the data is displayed in the console. We use the column mode and
turn on the headers. 
</p>

<pre>
sqlite> SELECT * FROM Cars;
Id          Name        Price     
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600 
</pre>

<p>
This is the data that we have written to the Cars table.
</p>


<h2>The last inserted row id</h2>

<p>
Sometimes, we need to determine the id of the last inserted
row. We use the <code>last_insert_row_id</code> method
to find it. 
</p>


<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.new ":memory:"
    
    db.execute "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)"
    db.execute "INSERT INTO Friends(Name) VALUES ('Tom')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Rebecca')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Jim')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Robert')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Julian')"
    
    id = db.last_insert_row_id
    puts "The last id of the inserted row is #{id}"
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end
</pre>

<p>
We create a Friends table in memory. The Id is automatically incremented. 
</p>

<pre class="explanation">
db.execute "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)"
</pre>

<p>
In SQLite, <code>INTEGER PRIMARY KEY</code> column is auto incremented. 
There is also an <code>AUTOINCREMENT</code> keyword. When used in 
<code>INTEGER PRIMARY KEY AUTOINCREMENT</code> a slightly different algorithm
for Id creation is used. 
</p>

<pre class="explanation">
db.execute "INSERT INTO Friends(Name) VALUES ('Tom')"
db.execute "INSERT INTO Friends(Name) VALUES ('Rebecca')"
db.execute "INSERT INTO Friends(Name) VALUES ('Jim')"
db.execute "INSERT INTO Friends(Name) VALUES ('Robert')"
db.execute "INSERT INTO Friends(Name) VALUES ('Julian')"
</pre>

<p>
These five SQL statements insert five rows into the Friends table. 
</p>

<pre class="explanation">
id = db.last_insert_row_id
</pre>

<p>
Using the <code>last_insert_row_id</code> method, we get the last inserted row id.  
</p>

<pre>
$ ./last_rowid.rb 
The last id of the inserted row is 5
</pre>

<p>
We see the output of the script. 
</p>


<h2>Fetching data</h2>

<p>
In the last example of this chapter we fetch some data. More about data 
fetching will be discussed in the Queries chapter. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
    
    stm = db.prepare "SELECT * FROM Cars LIMIT 5" 
    rs = stm.execute 
    
    rs.each do |row|
        puts row.join "\s"
    end
           
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    stm.close if stm
    db.close if db
end
</pre>

<p>
In the example we fetch 5 rows from the Cars table. 
</p>

<pre class="explanation">
stm = db.prepare "SELECT * FROM Cars LIMIT 5" 
rs = stm.execute 
</pre>

<p>
We prepare an SQL statement for execution with the <code>prepare</code> method. 
The method returns a statement object. Then the SQL statement is executed using
the <code>execute</code> method. It returns a result set. The ResutlSet object
is a simple cursor over the data that the query returns.
</p>

<pre class="explanation">
rs.each do |row|
    puts row.join "\s"
end
</pre>

<p>
With the <code>each</code> method we traverse the data in the result set. 
In each cycle, it returns a row. The row is an array of fields. These
fields are joined with a empty space to form a line. 
</p>

<pre>
$ ./fetch.rb 
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
</pre>

<p>
Output of the fetch.rb script. 
</p>

<p>
In this chapter of the SQLite Ruby tutorial, we have shown how to establish 
a database connection to the SQLite database. We have explained scripts
which do some basic work with a database.
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 13, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>
